#!/usr/bin/env python
# -*- coding: utf-8 -*-
import json
import unicodecsv as csv
import sys
import string
import codecs
from copy import copy
from hashlib import sha1
import hmac
import records

#file_path = sys.argv[1]
def encode(s):
    if s is string:
        return s.encode('utf-8')
    return ''

data = []

index = 0
cdn = 'http://op27dv62t.bkt.clouddn.com/'
with records.Database('mysql+pymysql://makex:P@ssw0rd@rm-wz9g0i87561m723dq.mysql.rds.aliyuncs.com:3306/makex?charset=utf8') as db:
    db.query('use makex;')
    createTable = "drop table if exists `nfc`; CREATE TABLE `nfc`( id INT(11) NOT NULL AUTO_INCREMENT , nfcid VARCHAR(255) not null , member_id INT(11) not null , team_id VARCHAR(50) not null ," \
                  " team_name VARCHAR(50) not null , member text COLLATE utf8_unicode_ci NOT NULL , PRIMARY KEY(id)) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;"

    db.query("SET NAMES 'utf8';")
    db.query(createTable)

    sql = "select organizes.`org_name` as orgName,organizes.`club_name` as club_name,`organizes`.`org_code` as orgCode, regs.* from regs inner join `organizes` on `organizes`.`id` = regs.`organize_id`  where `payment_status` <> 'fail' and `game_group_id` ='2' and regs.id not in ('2017063007098001','2017063037616427')"
    rawrows = db.query(sql)
    rows=json.loads(rawrows.export('json'))
    for rawrow in rows:
        print rawrow
	row = copy(rawrow)
	#print row
        r = []
        row['team'] = json.loads(row['team_json'])
        if row['subType'] == 'star4':
            row['subType'] = '4星级'
        elif row['subType'] == 'star5':
            row['subType'] = '5星级'

        #row['team_no'] = row['team']['teamNo'].encode('utf-8')
        row['team_no'] = row['team']['teamNo']
        #row['name'] = row['name'].encode('utf-8')
        #row['team_name'] = row['team']['name'].encode('utf-8')
        row['team_name'] = row['team']['name']
        row['logoFile'] = row['team']['logoFile']
        #row['orgName'] = row['orgName'].encode('utf-8')
        #row['club_name'] = row['club_name'].encode('utf-8')
        #row['address'] = row['address'].encode('utf-8')
        #row['consignee'] = row['consignee'].encode('utf-8')
        #if 'invoice_title' in row and row['invoice_title'] is not None:
        #    row['invoice_title'] = row['invoice_title'].encode('utf-8')
        #if 'invoice_detail' in row and row['invoice_detail'] is not None:
        #    row['invoice_detail'] = row['invoice_detail'].encode('utf-8')


        members = row['team']['members']
        row['total_member'] = len(members)

        row['male_teacher'] = 0
        row['female_teacher'] = 0
        row['male_student'] = 0
        row['female_student'] = 0
        try:
            row['male_teacher'] = len([x for x in members if x['type'] == 'teacher' and x['gender'] == 'male'])
            row['female_teacher'] = len([x for x in members if x['type'] == 'teacher' and x['gender'] == 'female'])
            row['male_student'] = len([x for x in members if x['type'] != 'teacher' and x['gender'] == 'male'])
            row['female_student'] = len([x for x in members if x['type'] == 'teacher' and x['gender'] == 'female'])
        except Exception as e:
            print e

        r.append(row)
        for m in members:
            mrow = copy(row)
            if m['type'] == 'captain':
                mrow['type'] = '队长'
            elif m['type'] == 'member':
                mrow['type'] = '队员'
            elif m['type'] == 'teacher':
                mrow['type'] = '老师'

            mrow['photo'] = cdn +m['photo']
            #m['name'] = m['name'].encode('utf-8')
            #mrow['member_name'] = m['name'].encode('utf-8')
            mrow['member_name'] = m['name']
            #mrow['mobile'] = m['mobile'].encode('utf-8')
            #mrow['idNum'] = m['idNum'].encode('utf-8')
            mrow['mobile'] = m['mobile']
            mrow['idNum'] = m['idNum']

            if m['idType'] == 'idCard':
                mrow['idType'] = '身份证'
            elif m['idType'] == 'passport':
                mrow['idType'] = '护照'
            elif m['idType'] == 'hui_xiang_zheng':
                mrow['idType'] = '回乡证'
            elif m['idType'] == 'tai_bao_zheng':
                mrow['idType'] = '台胞证'

            mrow['gender'] = '男' if m['gender'] == 'male' else '女'
            mrow['needHotel'] = '需要' if m['needHotel'] == True else '不需要'

            #mrow['residentAddress'] = m['residentAddress'].encode('utf-8')
            mrow['residentAddress'] = m['residentAddress']

            mrow['portrait'] = cdn + m['portrait']
            #mrow['email'] = m['email'].encode('utf-8')
            mrow['email'] = m['email']
            mrow['height'] = m['height']
            #nfc card
            index = index+1
            nfcid = '0952720170707'+str(index).rjust(8, '0')
            #print nfcid
            mrow['nfcid'] =hmac.new('makex.cc', nfcid, sha1).digest().encode('hex')+nfcid
            m['NFCID'] = mrow['nfcid']
            
            insert = "insert into nfc (nfcid,member_id,team_id,team_name,member) values('%s','%s','%s','%s','%s')" % (m['NFCID'].decode('utf-8'),m['id'],row['team']['id'],row['team']['name'],json.dumps(m,ensure_ascii=False))
            db.query(insert)
            r.append(mrow)

        update = "update regs set team_json = '%s' where id = '%s'" % (json.dumps(row['team'],ensure_ascii=False), row['id'])
        db.query(update)
        del row['team']

        data.extend(r)


    seq = ['0952720170707'+str(x).rjust(8, '0') for x in xrange(1000, 1100)]
    seq = [hmac.new('makex.cc', x, sha1).digest().encode('hex')+x for x in seq]
    for card in seq:
        row = dict()
        row['extra_card'] = card
        #print row
        data.append(row)



with open('output.csv', 'w') as out:
    out.write(codecs.BOM_UTF8)
    header = ['game_id','game_group_id', 'name', 'consignee', 'phone', 'address', 'team_no', 'team_name', 'male_teacher', 'female_teacher', 'male_student',
    'female_student','total_member', 'type', 'photo', 'member_name', 'mobile', 'idNum', 'idType', 'gender', 'needHotel','subType', 'residentAddress','portrait',
    'email','height','invoice_title','invoice_detail', 'orgName','orgCode','club_name', 'amount', 'nfcid', 'extra_card','logoFile']

    #header = ['game_id','game_group_id', 'name', 'consignee', 'phone', 'address', 'team_no', 'team_name', 'male_teacher', 'female_teacher', 'male_student',
    #          'female_student','total_member', 'type', 'photo', 'name', 'mobile', 'idNum', 'idType', 'gender', 'needHotel','subType', 'residentAddress','portrait',
    writer = csv.DictWriter(out, header, extrasaction='ignore')
    writer.writeheader()
    writer.writerows(data)





